COSC 2206: Internet Tools
Installing JDBC drivers for MySQL

Introduction

Java can connect to databases using the JDBC (Java Data Base Connectivity) interface and a database specific driver. In our case we are using MySQL and it is assumed that you already have MySQL and Java installed on your computer.

Installing JDBC driver for MySQL and Windows

The latest production version of the MySQL JDBC Driver is available at the MySQL web site. You can get the driver installation zip file either from the CD at /software/jdbc-driver/mysql-connector-java-3.0.14-production.zip, or you can download this file at www.mysql.com.

Unzip it into a directory ( I used c:\jdbc-driver) and you should have the directory structure

The subdirectory mysql-connector-java-3.0.14-production contains the classes we need:

It also contains a README file that you can read for information on the driver and implementation. There is also a Docs directory containing documentation.

The jar file mysql-connector-java-3.0.14-production-bin.jar contains all the driver classes. Copy it to a directory where you keep your packages. I use c:\packages. You can rename it there to a shorter name: I use mysql-jdbc-driver.jar. Also copy the com subdirectory to your package directory. The com directory contains the unjarred driver classes. The full package name of the Driver class is

com.mysql.jdbc.Driver

You should now have a packages directory like

Copying the com folder to your packages directory gives you two ways to run classes that require the driver:

java -cp .;c:\packages;c:\packages\mysql-jdbc-driver.jar MyClass
java -cp .;c:\packages MyClass

The first way uses the jar file and the second way uses the classes in com. (NOTE: when using jar files it is necessary to define them explicitly in the classpath. It is not enough to just give the directory containing them.) It is not necessary to specify where the driver classes are when compiling a class using javac since the driver is loaded dynamically at run time. The jar file will be needed when we study Java Servlets using the Tomcat server and it will need to be placed in another directory since the server defines its own classpath.

A database to use for testing

We assume you have already created the web_db database containing the books table from the file books.sql (see MySQL installation notes) and repeated here (books.sql):

# File: books.sql
# A sample database of books for an online bookstore

CREATE DATABASE IF NOT EXISTS web_db;
USE web_db;
DROP TABLE IF EXISTS books;

# The books table holds all the information on the books in the bookstore

CREATE TABLE books 
(
  isbn   CHAR(15)       PRIMARY KEY NOT NULL,
  title  VARCHAR(100)   NOT NULL,
  author VARCHAR(100)   NOT NULL,
  pub    VARCHAR(20)    NOT NULL,
  year   year           NOT NULL,
  price  DECIMAL(9,2)   DEFAULT NULL
);

# Insert a few books for testing

INSERT INTO books VALUES (
   '0-672-31784-2',
   'PHP and MySQL Web Development',
   'Luke Welling, Laura Thomson',
   'Sams', 2001, 74.95
);

INSERT INTO books VALUES (
   '0-13-066190-2', 
   'Core MySQL', 
   'Leon Atkinson',
   'Prentice Hall PTR', 2001, 68.00
);

INSERT INTO books VALUES (
   '1-861003-02-1',
   'Professional Apache',
   'Peter Wainwright',
   'Wrox Press Ltd', 1999, 74.95
);

INSERT INTO books VALUES (
   '0-13-089793-0',
   'Core WEB Programming, 2nd Ed',
   'Marty Hall, Larry Brown',
   'Prentice Hall PTR', 2001, 75.00
);

INSERT INTO books VALUES (
   '0-672-31880-6',
   'CGI in 24 Hours',
   'Rafe Colburn',
   'Sams', 2000, 37.95
);

INSERT INTO books VALUES (
   '1-861003-14-5',
   'Beginning Perl',
   'Simon Cozens',
   'Wrox Press Ltd', 2000, 59.95
);

INSERT INTO books VALUES (
   '0-596-00027-8',
   'Programming Perl, 3rd Ed',
   'Larry Wall, Tom Christianson, Jon Orwant',
   'O\'Reilly', 2000, 72.95
);

INSERT INTO books VALUES (
   '1-56592-243-3',
   'Perl Cookbook',
   'Tom Christianson, Nathan Torkington',
   'O\'Reilly', 1999, 56.95
);

Some tester classes

We now illustrate how to use Java classes and JDBC to communicate with a MySQL database. There are four classes which are available individually below or all together in the zip file examples.zip which could be unzipped into some temporary directory (I used c:\jdbc-test). This zip file is also on the CD in directory \install-notes\jdbc\.

Testing the database connection

We now write a small class that tests the connection to MySQL. The first step is to load the driver at runtime using the statement

Class.forName("com.mysql.jdbc.Driver");

This statement can throw a ClassNotFoundException.

The next step is to make a connection to a MySQL database. The database is specified using the URL-like string

"jdbc:mysql://localhost:3306/myD?user=myU&password=myP"

where you should replace myD by the name of your database such as web_db, myU with your user name, and myP by your password. Now the connection is made with the statement

Connection conn = DriverManager.getConnection("URL-like string");

For example, you could use a statement such as

Connection conn = DriverManager.getConnection(
   "jdbc:mysql://localhost:3306/web_db?user=c2206&password=c2206");

or you could use variables for parts of the string:

Connection conn = DriverManager.getConnection(
   "jdbc:mysql://" + host + ":" + port + "/" + database +
   "?user=" + user + "&password=" + password);

The getConnection method can throw an SQLException. Therefore the load and connect can be done using the statements

try
{
   Class.forName("com.mysql.jdbc.Driver");

}
catch (ClassNotFoundException e)
{
   // handle exception here
}
try
{
   Connection conn = DriverManager.getConnection("URL-like string");
}
catch (SQLException e)
{
   // handle exception here
}

When you have finished accessing the database the connection can be closed using

conn.close();

Here is a short class (ConnectionTester.java) that can be used to test a database connection. It just makes a connection and closes it (note that the java.sql package contains the various sql classes and interfaces):

import java.sql.*;

/*
   Testing the JDBC MySQL driver installation. This program just
   opens a connection and then closes it and displays a message
*/

public class ConnectionTester
{   
   public static void main(String[] args) 
   {
      // Connection parameters

      String host = "localhost"; // default value
      String port = "3306";      // default value
      String user = "";          // command line arg 0
      String password = "";      // command line arg 1
      String database = "";      // command line arg 2

      if (args.length == 3)
      {
         user = args[0];
         password = args[1];
         database = args[2];
      }
      else
      {
         System.out.println("args: user password database");
         System.exit(0);
      }

      try 
      {  
         // Load the driver class dynamically.
         // This creates an instance of the class
   
         Class.forName("com.mysql.jdbc.Driver");

         // Make a database connection and close it

         Connection conn = DriverManager.getConnection(
            "jdbc:mysql://" + host + ":" + port + "/" + database + 
            "?user=" + user + "&password=" + password);
         conn.close();
      }

      catch (ClassNotFoundException e) 
      {
         System.out.println("Could not find the MySQL driver");
         System.exit(0);
      }

      catch (SQLException e)
      {
         System.out.println(e.getMessage());
         System.exit(0);
      }
      System.out.println("Connection to " + database + " was successful");
   }   
}

To compile it use the command

javac ConnectionTester.java

To run it for the test database use the command

java -cp .;c:\packages ConnectionTester user password test

replacing user by your user name and password by your password. If all goes well you should see the message

Connection to test was successful

Here is another program (MySQLTester.java) that shows how to execute a query statement and return the results.

import java.sql.*;

/*
   A simple demo showing how to use JDBC and the jdbc driver
   to connect to a MySQL database and execute a query.
   The command line arguments are the user name, the password,
   the database name, and a query string enclosed in double
   quotes.
   
   The connection uses localhost and the default MySQL port 3306.
*/

public class MySQLTester
{   
   private String host = "localhost";
   private String port = "3306";
    
   public static void main(String[] args) 
   { 
      MySQLTester prog = new MySQLTester();
      if (args.length == 4)
      {
         prog.run(args[0], args[1], args[2], args[3]);
      }
      else
      {
         System.out.println("Args: user password database \"query\"");
      }
   }
    
   public void run(String user, String password, String database,
      String query ) 
   {
      try 
      {
         // Load the driver class dynamically
         // This creates an instance of the class

         Class.forName("com.mysql.jdbc.Driver");
      }
      catch (ClassNotFoundException e) 
      {
         System.out.println("Could not find the MySQL driver");
         System.exit(0);
      }
      try 
      {
        // Create a connection using the mysql driver and return the
        // connection object

        Connection conn = DriverManager.getConnection(
           "jdbc:mysql://" + host + ":" + port + "/" + database + 
           "?user=" + user + 
           "&password=" + password);    
    
        // Execute a query on the connection object using a Statement
        // object. The results are returned in the ResultSet object.

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);   // table rows

        // The ResultSetMetaData object contains information about
        // the database table such as the column names and the number
        // of columns in the table.

         ResultSetMetaData rsmd = rs.getMetaData();
         int numberOfColumns = rsmd.getColumnCount();

         // display column names (indices begin at 1) using the
         // colon as a field separator

         for (int i = 1; i <= numberOfColumns; i++)
         {
            System.out.print(rsmd.getColumnName(i));
            if (i < numberOfColumns) System.out.print(":");
         }
         System.out.println();
	
         // Use the ResultSet iterator to iterate through each row
         // of the table and display the data for each row using the
         // colon as a field separator.

         while (rs.next())
         {
            for (int i = 1; i <= numberOfColumns; i++)
            {
               String columnValue = rs.getString(i);
               System.out.print(columnValue);
               if (i < numberOfColumns) System.out.print(":");
            }
            System.out.println();	
         }
         
         rs.close();
         conn.close();
      }
      catch (SQLException e)
      {
         System.out.println(e.getMessage());
         System.exit(0);
      }
   }
}

Assuming that you have created the books table in the web_db database, compile it and run it using the command

java -cp .;c:\packages MySQLTester user password web_db
   "SELECT * FROM books"

replacing user and password by your values. The results displayed are

isbn:title:author:pub:year:price
0-672-31784-2:PHP and MySQL Web Development:Luke Welling, Laura Thomson:
   Sams:2001:74.95
0-13-066190-2:Core MySQL:Leon Atkinson:Prentice Hall PTR:2001:68.00
1-861003-02-1:Professional Apache:Peter Wainwright:Wrox Press Ltd:
   1999:74.95
0-13-089793-0:Core WEB Programming, 2nd Ed:Marty Hall, Larry Brown:
   Prentice Hall PTR:2001:75.00
0-672-31880-6:CGI in 24 Hours:Rafe Colburn:Sams:2000:37.95
1-861003-14-5:Beginning Perl:Simon Cozens:Wrox Press Ltd:2000:59.95
0-596-00027-8:Programming Perl, 3rd Ed:Larry Wall, Tom Christianson, Jon Orwant:
   O'Reilly:2000:72.95
1-56592-243-3:Perl Cookbook:Tom Christianson, Nathan Torkington:
   O'Reilly:1999:56.95

Testing database connection using a servlet

To use the MySQL JDBC driver with servlets it is necessary to place it in a special directory of your web application so that Tomcat can find it. We will use the test web application created in the Tomcat 5 install notes.

First copy the file mysql-jdbc-driver.jar from your packages directory to the directory c:\tomcat\webapps\test\WEB-INF\lib. When Tomcat runs your servlet it will search this directory.

Here is a simple class (DBaseTestServlet.java) that you can use to test JDBC with servlets. Here we assume that the user name is c2206 and the password is also c2206.

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
   Test servlet for JDBC to perform the following operations on
   a table called test in the test database (this database is
   always available for testing in MySQL).
  
   <pre>
      DROP TABLE IF EXISTS test;
      CREATE TABLE test ( name VARCHAR(20) NOT NULL, 
         age INTEGER NOT NULL );
      INSERT INTO test VALUES ('Fred', 34);
      SELECT * FROM test;
      Display the result
      DROP TABLE test;
   </pre>
   For a more object-oriented version see BookDisplayServlet
*/

public class DBaseTestServlet extends HttpServlet
{
   private Connection connection;

   /** Load the database driver and make a connection
   */
   public void init() throws ServletException
   {
      try
      {
         Class.forName("com.mysql.jdbc.Driver");
         connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/web_db", "c2206", "c2206");
      }
      catch (ClassNotFoundException e)
      {
         System.out.println("Cannot load driver");
      }
      catch (SQLException e)
      {
         System.out.println("Cannot make a connection");
      }
   }

   /**
      Execute some SQL statements and queries
   */
   public void doGet(HttpServletRequest request,
      HttpServletResponse response)
      throws IOException, ServletException
   {
      String name = "";
      int age = 0;

      response.setContentType("text/html");
      PrintWriter out = response.getWriter();

      try
      {
         Statement st = connection.createStatement();
         st.execute("DROP TABLE IF EXISTS test");
         st.execute(
              "CREATE TABLE test ("
            + "name VARCHAR(20) NOT NULL,"
            + "age INTEGER NOT NULL )" );
         st.execute("INSERT INTO test VALUES ('Fred', 34)");

         ResultSet result = st.executeQuery("SELECT * FROM test");

         result.next(); // only one row
         name = result.getString("name");
         age = result.getInt("age");

         st.execute("DROP TABLE test");
      }
      catch (SQLException e)
      {
         System.out.println("Error in statement");
      }

      out.println("<html>\n"
         + "<head><title>Testing JDBC</title></head>\n"
         + "<body>\n"
         + "The name is " + name + "<br />"
         + "The age is " + age
         + "</body>\n"
         + "</html>\n" );
   }

   /** Free database resources and close connection
   */
   public void destroy()
   {
      try
      {
         connection.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}

Since this class is in a package called dbase copy it into the directory c:\tomcat\webapps\test\WEB-INF\classes\dbase\ and compile it from the dbase directory using the compiler command (if you have created the special command prompt testservlets as indicated in the Tomcat installation notes).

javac DBaseTestServlet.java

If you haven't created a custom prompt you must use the longer command

javac -classpath .;c:\tomcat\webapps\test\WEB-INF\classes;
         c:\tomcat\common\lib\servlet-api.jar  DBaseTestServlet.java

Here we are specifying a path containing the current directory, the classes directory of the test web application, and the location of the directory for the servlet classes contained in the servlet.jar file. These classes are not part of the standard Java SDK so the jar file must be included in the classpath.

We don't use the Java interpreter for servlets since Tomcat is responsible for loading and running servlets. This is analogous to applets where the browser runs the applet.

To run the servlet make sure MySQL and Tomcat are running and type the URL

http://localhost:8080/test/servlet/dbase.DBaseTestServlet

into your browser.

This URL uses the default servlet invoker instead of an explicit servlet to URL mapping rule. This is indicated by the use of the name servlet in the URL and the presence of the full package name dbase.DBaseTestServlet of the servlet. You should see the following output in your browser:

A Servlet to display the books table

The following servlet BookDisplayServlet0.java shows how to connect to the books table and display it as an HTML table.

package dbase;

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
   Test servlet for JDBC:

   Display the books table as an HTML table.
   This servlet assumes that this table exists with the following
   structure
   <pre>
      CREATE TABLE books
      (
         isbn CHAR(15)        PRIMARY KEY NOT NULL,
         title VARCHAR(100)   NOT NULL,
         author VARCHAR(100)  NOT NULL,
         pub VARCHAR(20)      NOT NULL,
         year year            NOT NULL,
         price DECIMAL(9,2)   DEFAULT NULL
      };
   </pre>
*/

public class BookDisplayServlet0 extends HttpServlet
{
   private Connection connection;
   private PreparedStatement getBooks;

   /** Initialize the servlet by connecting to the database
      and preparing a select query for the books in the books table.
   */
   public void init() throws ServletException
   {
      try
      {
         // Load the driver: tomcat will use the classpath 
         // webapps\test\WEB-INF\lib to find the jar file containing
         // the classes. Note that the java compiler does not need
         // to know the whereabouts of the driver since the driver
         // is loaded dynamically at run time.
         
         Class.forName("com.mysql.jdbc.Driver");
         
         // Make a database connection to the bookstore database
         
         connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/web_db", "c2206", "c2206"); 
            
         // Prepare a statement that can be used to send the query
            
         getBooks = connection.prepareStatement("SELECT * FROM books");
      }
      catch (Exception e)
      {
         e.printStackTrace();
         throw new UnavailableException(e.getMessage());
      }
   }

   /**
      Execute the SQL query and display the books table
   */
   public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws IOException, ServletException
   {
      response.setContentType("text/html");
      PrintWriter out = response.getWriter();

      try
      {
         ResultSet book = getBooks.executeQuery();
         ResultSetMetaData rsmd = book.getMetaData();
         int numColumns = rsmd.getColumnCount();

         out.println(
           "<html>\n"
         + "<head><title>Displaying the books database table"
         + "</title></head>\n"
         + "<body>\n"
         + "<h1>Displaying the books database table</h1>\n"
         + "<table border=\"1\">");

         // display column names using information provided by the
         // ResulSettMetaData object associated with the ResultSet object

         out.println("<tr>");
         for (int col = 1; col <= numColumns; col++) // index begins at 1
         {
            out.println("<th>" + rsmd.getColumnName(col) + "</th>");
         }
         out.println("</tr>");

         // Display rows of table using the table data in the ResultSet
         // object. Each entry in a row can be returned as a string using
         // getString(1), getString(2), ... (index begins at 1 not 0)

         while(book.next())
         {
            out.println("<tr>");
            for (int col = 1; col <= numColumns; col++)
            {
               out.println("<td>" + book.getString(col) + "</td>");
            }
            out.println("</tr>");
         }
         out.println("</table>\n</html>");
         out.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
         out.println(
              "<html>\n"
            + "<head><title>SQL Error</title></head>\n"
            + "<body>\n"
            + "<h1>SQL Error</h1>\n"
            + "<p>A database error occurred</p>"
            + "</body></html>" );
         out.close();
      }
   }

   /** Free database resources and close connection
   */
   public void destroy()
   {
      try
      {
         getBooks.close();
         connection.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}

javac BookDisplayServlet0.java

If you haven't created a custom prompt you must use the longer command

javac -classpath .;c:\tomcat\webapps\test\WEB-INF\classes;
         c:\tomcat\common\lib\servlet.jar  BookDisplayServlet0.java

We don't use the Java interpreter for servlets since Tomcat is responsible for loading and running servlets. This is analogous to applets where the browser runs the applet.

To run the servlet make sure MySQL and Tomcat are running and type the URL

http://localhost:8080/test/servlet/dbase.BookDisplayServlet0

into your browser.

This URL uses the default servlet invoker instead of an explicit servlet to URL mapping rule. This is indicated by the use of the name servlet in the URL and the presence of the full package name dbase.DisplayBookServlet0 of the servlet. You should see the following output in your browser:

The final test Web Application

The test web application created in Tomcat installation notes also contains the two database servlets discussed in these JDBC notes. It is available at here. Just unzip it into some temporary directory and then move the resulting test directory into your webapps directory.

However, you will learn more if you create it step by step yourself by following these notes and the Tomcat notes.

When you have finished creating your test application you can replace it by the one in test.zip which has an expanded index.html file.

We have also included servlet mappings in web.xml for the two database servlets:

...
...
...
<servlet>
   <servlet-name>Example3</servlet-name>
   <servlet-class>dbase.DBaseTestServlet</servlet-class>
</servlet>

<servlet>
   <servlet-name>Example4</servlet-name>
   <servlet-class>dbase.BookDisplayServlet0</servlet-class>
</servlet>
...
...
...
<servlet-mapping>
   <servlet-name>Example3</servlet-name>
   <url-pattern>/dbasetest</url-pattern>
</servlet-mapping>

<servlet-mapping>
   <servlet-name>Example4</servlet-name>
   <url-pattern>/bookdisplay</url-pattern>
</servlet-mapping>

Again, don't forget that the servlet tags must all precede the servlet-mapping tags.

The database servlets can now be referenced using the URL's

http://localhost:8080/test/dbasetest
http://localhost:8080/test/bookdisplay

Inside the index.html page they can be referenced using links such as

<a href="dbasetest">dbasetest</a>
<a href="bookdisplay">bookdisplay</a>

To try the finished test application use the link

http://localhost:8080/test

to display the home page.

Here is the final directory structure of the test application.

 test (dir)
   |
   +--- images (dir)
   |
   +--- jsp (dir)
   |        |
   |        +--- hello.jsp
   |
   +--- txt (dir)
   |        |
   |        +--- text versions of files for display
   |
   +--- WEB-INF
   |        |
   |        +--- classes (dir)
   |        |         |
   |        |         +--- dbase (dir)
   |        |         |         |
   |        |         |         +--- BookDisplayServlet0.java
   |        |         |         |
   |        |         |         +--- BookDisplayServlet0.class
   |        |         |         |
   |        |         |         +--- DBaseTestServlet.java
   |        |         |         |
   |        |         |         +--- DBaseTestServlet.class
   |        |         |         |
   |        |         |         +--- books.sql
   |        |         |
   |        |         +--- mypackage (dir)
   |        |         |         |
   |        |         |         +--- HelloWorld.java
   |        |         |         |
   |        |         |         +--- HelloWorld.class
   |        |         |
   |        |         +--- HelloWorld.java
   |        |         |
   |        |         +--- HelloWorld.class
   |        |
   |        +--- lib (dir)
   |        |         |
   |        |         +--- mysql-jdbc-driver.jar
   |        |
   |        +--- web.xml
   |
   +--- index.html

JDBC Documentation and Tutorials

If you have installed the Java documentation then you can find some JDBC documentation at

c:\j2sdk1.4.2\docs\guide\jdbc\index.html

and links to tutorials.

COSC 2206: Internet Tools Installing JDBC drivers for MySQL